
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DataStore {
	

	Connection conn;
    Statement stat;
    ArrayList<User> userList;
    ArrayList<Item> itemList;
    ArrayList<Item> sellersItemList;
    ArrayList<Auction> auctionList;
    
	DataStore() throws ClassNotFoundException, SQLException{

		Class.forName("org.sqlite.JDBC");
		conn = DriverManager.getConnection("jdbc:sqlite:test.db");
		stat = conn.createStatement();
		
		//TODO add more resume logic
		//stat.executeUpdate("drop table if exists clients;");
		//stat.executeUpdate("drop table if exists items;");
		//stat.executeUpdate("drop table if exists transactionRecords;");
	
	    stat.executeUpdate("create table if not exists clients (name, ip, port)");
	    stat.executeUpdate("create table if not exists items (itemId, description, startBid, currentBid, sellerName, sellerIP, sellerPort, bidderName, bidderIp, bidderPort)");
	    stat.executeUpdate("create table if not exists transactionRecords (itemId, description, winningBid, clientName, clientIp, clientPort)");
	    
	    userList = new ArrayList<User>();
	    itemList = new ArrayList<Item>();
	    sellersItemList = new ArrayList<Item>();
		auctionList = new ArrayList<Auction>();
		
	    reloadAuctions();
	    reloadloadUsers();
	    reloadItems();

	}

	public void addClient(String name,String ipAddress, int port ) throws SQLException{
	
        PreparedStatement prep = conn.prepareStatement("insert into clients values (?, ?, ?);");

        prep.setString(1, name);
        prep.setString(2, ipAddress);
        prep.setInt(3, port);
        
        prep.addBatch();
    
        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);
        
        userList.add(new User(name,ipAddress,port));
		
	}
	
	public void addItem(String itemId, String description, float startBid, String sellerName, String sellerIp, int sellerPort) throws SQLException{
		
		 PreparedStatement prep = conn.prepareStatement("insert into items values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

	     prep.setString(1, itemId);
	     prep.setString(2, description);
	     prep.setFloat(3, startBid);
	     prep.setFloat(4, startBid);
	     prep.setString(5, sellerName);
	     prep.setString(6, sellerIp);
	     prep.setInt(7,sellerPort);
	     prep.setString(8, "noone");
	     prep.setString(9, "nowhere");
	     prep.setInt(10, 9001);
	        
	     prep.addBatch();
	    
	     conn.setAutoCommit(false);
	     prep.executeBatch();
	     conn.setAutoCommit(true);
	     
	     itemList.add(new Item(itemId, description, startBid, startBid));		
		
	}
	
	public void addTransaction(String itemId, String description, float bid, String client, String ip, int port) throws SQLException{
		
		 PreparedStatement prep = conn.prepareStatement("insert into transactionRecords values (?, ?, ?, ?, ?, ?);");

	     prep.setString(1, itemId);
	     prep.setString(2, description);
	     prep.setFloat(3, bid);
	     prep.setString(4, client);
	     prep.setString(5, ip);
	     prep.setInt(6, port);
	        
	     prep.addBatch();
	    
	     conn.setAutoCommit(false);
	     prep.executeBatch();
	     conn.setAutoCommit(true);
	}
	
	public void removeItem(String itemId) throws SQLException{
		
		stat.executeUpdate("delete from items where itemId ='"+itemId+"';");
		System.out.println("Removed Client from DB : "+itemId);
		
	}
	
	public boolean itemExists(String description) throws SQLException{
		 ResultSet rs = stat.executeQuery("select * from items where description ='"+description+"';");

	        int count = 0;
	        while (rs.next()) {
	            System.out.println("ItemId = " + rs.getString("itemId"));
	            System.out.println("description = " + rs.getString("description"));
				System.out.println("startBid = " + rs.getString("startBid"));
				System.out.println("currentBid = " + rs.getString("currentBid"));
				count++;
			}
	        rs.close();
	        
	        if(count >= 1){
	        	
	        	if(count > 1){
	        		
	        		System.out.println("MOAR that one entry with "+description);
	        	}
	        	return true;
	        	
	        } else {
	        	
	        	System.out.println("No entry with "+description);
	        	return false;
	        }
	} 
	
	public Item getItemById(String itemId) throws SQLException{
		 ResultSet rs = stat.executeQuery("select * from items where itemId ='"+itemId+"';");

		 	Item item = null;
	        int count = 0;
	        while (rs.next()) {
	            System.out.println("ItemId = " + rs.getString("itemId"));
	            System.out.println("description = " + rs.getString("description"));
				System.out.println("startBid = " + rs.getFloat("startBid"));
				System.out.println("currentBid = " + rs.getFloat("currentBid"));
				item = new Item(rs.getString("itemId"), rs.getString("description"), rs.getFloat("startBid"), rs.getFloat("currentBid"));
				count++;
			}
	        rs.close();
	        
	        if(count >= 1){
	        	
	        	if(count > 1){
	        		
	        		System.out.println("MOAR that one entry with "+itemId);
	        	}
	        	return item;
	        	
	        } else {
	        	
	        	System.out.println("No entry with "+itemId);
	        	return null;
	        }
	} 
	
	public void updateItem(String itemId, float current, String bidderName, String bidderIp, int bidderPort) throws SQLException{
		
		stat.executeUpdate("update items set currentBid = '"+current+"' where itemId = '"+itemId+"';");
		stat.executeUpdate("update items set bidderName = '"+current+"' where itemId = '"+itemId+"';");
		stat.executeUpdate("update items set bidderIp = '"+current+"' where itemId = '"+itemId+"';");
		stat.executeUpdate("update items set bidderPort = '"+current+"' where itemId = '"+itemId+"';");
		System.out.println("Updated Item "+itemId+" in DB");		
		
	}
	
	public void reloadAuctions() throws SQLException{
		
        ResultSet rs = stat.executeQuery("select * from items;");
    	auctionList.clear();
        while (rs.next()) {
    		
        	auctionList.add(new Auction(rs.getString("itemId"),rs.getString("description"),Float.valueOf(rs.getString("startBid")),Float.valueOf(rs.getString("currentBid")),
        				rs.getString("sellerName"),rs.getString("sellerIp"), rs.getInt("sellerPort")));
		
        }
        rs.close();
		
	}
	
	public void reloadItems() throws SQLException{
		
        ResultSet rs = stat.executeQuery("select * from items;");
    	itemList.clear();
        while (rs.next()) {
        	itemList.add(new Item(rs.getString("itemId"),rs.getString("description"),Float.valueOf(rs.getString("startBid")),Float.valueOf(rs.getString("currentBid"))));
        }
        rs.close();
		
	}
	
	public void reloadloadUsers() throws SQLException{
		
        ResultSet rs = stat.executeQuery("select * from clients;");
    	userList.clear();
        while (rs.next()) {
    		
        	userList.add(new User(rs.getString("name"),rs.getString("ip"),Integer.valueOf(rs.getString("port"))));
		
        }
        rs.close();
		
	}
	
	public void removeClient(String name) throws SQLException{
		
		stat.executeUpdate("delete from clients where name = '"+name+"';");
		System.out.println("Removed Client from DB : "+name);
		reloadloadUsers();

		
	}
     
	public boolean clientExists(String name) throws SQLException{


        ResultSet rs = stat.executeQuery("select * from clients where name ='"+name+"';");

        int count = 0;
        while (rs.next()) {
		
            System.out.println("name = " + rs.getString("name"));
            System.out.println("ip = " + rs.getString("ip"));
			System.out.println("port = " + rs.getString("port"));
			count++;
		}
        rs.close();
        
        if(count >= 1){
        	
        	if(count > 1){
        		
        		System.out.println("MOAR that one entry with "+name);
        	}
        	return true;
        	
        } else {
        	
        	System.out.println("No entry with "+name);
        	return false;
        }
	}

	public boolean clientIsLeadBidder(String name) throws SQLException {
		// TODO Auto-generated method stub

        ResultSet rs = stat.executeQuery("select * from items where bidderName ='"+name+"';");

        int count = 0;
        while (rs.next()) {

        	count++;
		}
        rs.close();
        
        if(count >= 1){
        	
        	System.out.println(name+" is lead bidder on one or more items");
        	return true;
        	
        } else {
        	
        	System.out.println("No entry with "+name);
        	return false;
        }
	}

	public boolean clientIsSellingItem(String name) throws SQLException {
		// TODO Auto-generated method stub
		 ResultSet rs = stat.executeQuery("select * from items where sellerName ='"+name+"';");

	        int count = 0;
	        while (rs.next()) {
			
				count++;
			}
	        rs.close();
	        
	        if(count >= 1){
	        	
	        	System.out.println(name+" is selling one or more Items");
	        	return true;
	        	
	        } else {
	        	
	        	System.out.println("No entry with "+name);
	        	return false;
	        }
	}

	public void getSellerItems(String name) throws SQLException {
		ResultSet rs = stat.executeQuery("select * from items where sellerName ='"+name+"';");
    	sellersItemList.clear();
        while (rs.next()) {
    		
        	sellersItemList.add(new Item(rs.getString("itemId"),rs.getString("description"),Float.valueOf(rs.getString("startBid")),Float.valueOf(rs.getString("currentBid"))));
		
        }
        rs.close();
		
	} 
	
}
	

